# coding=utf-8

import pyodbc
import sys
import os
import re
from DBConnection import DBConnect
# from github import Github  # pip install PyGithub
# from github import Auth
import datetime
import git  # pip install gitpython
from timezone_conversion import timezone_conversion


DATABASE=sys.argv[1].upper()

if DATABASE == 'GBOS':
    branch = 'GBOS-BOSSQL-GBOS'
    folder_manual = 'GBOS-Database-Manual\BOSSQL\GBOS'
    branch_keyword = '/BOSSQL/GBOS/'
    file_groups = ['[Account]', '[EventFeed]', '[Transaction]', '[FundTransfer]', '[Request]', '[Verification]', '[Interest]']
elif DATABASE == 'IFT':
    branch = 'GBOS-BOSSQL-IFT'
    folder_manual = 'GBOS-Database-Manual\BOSSQL\IFT'
    branch_keyword = '/BOSSQL/IFT/'
    file_groups = ['[IFT]']
elif DATABASE == 'CARE':
    branch = 'GBOS-BOSCRMSQL-CARE'
    folder_manual = 'GBOS-Database-Manual\BOSCRMSQL\CARE'
    branch_keyword = '/BOSCRMSQL/CARE/'
    file_groups = ['[CRM]', '[IVR]']
elif DATABASE == 'CAS':
    branch = 'GBOS-BOSCRMSQL-CAS'
    folder_manual = 'GBOS-Database-Manual\BOSCRMSQL\CAS'
    branch_keyword = '/BOSCRMSQL/CAS/'
    file_groups = ['[CAS]']
elif DATABASE == 'VIP':
    branch = 'GBOS-BOSCRMSQL-VIP'
    folder_manual = 'GBOS-Database-Manual\BOSCRMSQL\VIP'
    branch_keyword = '/BOSCRMSQL/VIP/'
    file_groups = ['[Request]', '[Response]']
elif DATABASE == 'IDEA':
    branch = 'GBOS-BOSTOKENSQL-IDEA'
    folder_manual = 'GBOS-Database-Manual\BOSTOKENSQL\IDEA'
    branch_keyword = '/BOSTOKENSQL/IDEA/'
    file_groups = []
else:
    branch = ''
    print('The parameter is not valid')
    exit(1)

if DATABASE == 'IDEA':
    server = 'dv-gen-eus-tkn-001-sql.database.windows.net'
else:
    server = 'dv-gen-eus-001-sql.database.windows.net'

print('get started...')

# DB connect configuration
DBConnetion = DBConnect(serverName='G2CPDBETLS01',databaseName='DataEngineering')
conn_etl = DBConnetion.openConn()
cursor_etl = conn_etl.cursor()

conn_config = (
    "Driver={ODBC Driver 17 for SQL Server};"
    f"Server={server};"
    f"Database=WMT-{DATABASE};"
    "UID=vincent.tang@greendotcorp.com;"
    "PWD=xxx;"
    "Authentication=ActiveDirectoryInteractive;"
)

############################################## Deploy Manual
branch_manual = 'GBOS-Database-Manual'
repo_path_manual = rf"C:\work\code\{branch_manual}"
repo_manual = git.Repo(repo_path_manual)
if repo_manual.active_branch != 'main':
    repo_manual.git.checkout('main')
print("Pull from GitHub: " + branch_manual)
repo_manual.remotes.origin.pull()

# Retrieve the deployment sequence from the control table
today = datetime.date.today()
lookBackDay = 1
#endDate = today.strftime("%Y-%m-%d")
endDate = '2025-03-03'
#startDate = (today + datetime.timedelta(days=-lookBackDay)).strftime("%Y-%m-%d")
startDate = '2025-02-16'
SQL21 = ("SELECT * FROM ( "
        f"SELECT RIGHT(DBSourceCode,LEN(DBSourceCode)-CHARINDEX('/{DATABASE}/',DBSourceCode)-1-LEN('{DATABASE}')) AS FileName, CodeSubmitUTCDate "
         ",ROW_NUMBER() OVER(PARTITION BY DBSourceCode ORDER BY CodeSubmitUTCDate) AS num "
         "FROM dbo.DBESubmittedDBCode "
        f"WHERE DBSourceCodeBranch = '{branch_manual}' AND ReviewStatus='APPROVED' "
        f"AND CodeSubmitUTCDate >= '{startDate}' AND CodeSubmitUTCDate < '{endDate}' "
        f"AND DBSourceCode like '%{branch_keyword}%' "
         "AND JiraID NOT LIKE '%Merge branch%' "
         #"AND DBESubmittedDBCodeKey=19683 "
         #"AND DBSourceCode like '%IX_AccountStatusReasonHistory_Active_StatusReasonKey_IAccountKey_20250211%' "
         ") as a WHERE num=1 "
         "ORDER BY CodeSubmitUTCDate ")
print('SQL:', SQL21)
cursor_etl.execute(SQL21)
deployment_sequence = cursor_etl.fetchall()
print('Get FileName from DBESubmittedDBCode')

# Connect to the Azure SQL database
conn = pyodbc.connect(conn_config)
cursor = conn.cursor()
print('Connect to target server')

# Deploy each table creation script based on the sequence
folder_path_manual = rf"C:\work\code\{folder_manual}"

#print('deployment_sequence:', deployment_sequence)
keyword11 = "CONVERT([datetime],((getdate() AT TIME ZONE 'UTC') AT TIME ZONE 'Pacific Standard Time'))"
keyword12 = "CONVERT([datetime],((sysdatetime() AT TIME ZONE 'UTC') AT TIME ZONE 'Pacific Standard Time'))"
keyword13 = "CAST(getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' as datetime)"
keyword14 = "CAST(SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' as datetime)"
exclude_manual = ('Data/BackFill_OverdraftAccountSubscriptionKey_PART2_20241004.sql'
                ,'Table/GBOS-131809_IX_FundTransfer_InitiatorAccountIdentifier_20250228.sql'
           )
for row in deployment_sequence:
    file_name = row.FileName
    print('file_name:', file_name)
    script_file_path = os.path.join(folder_path_manual, file_name)

    if os.path.exists(script_file_path) and file_name not in exclude_manual:
        print(script_file_path, 'exists')
        #with open(script_file_path, 'r', encoding="utf8") as file:
        with open(script_file_path, 'r') as file:
            script_content = file.read()
            script_content_upper = script_content.upper()
            if 'CREATE USER' not in script_content and 'EXEC sp_addrolemember' not in script_content and 'Pubs.' not in script_content:
                # replace Database
                print('use db:', f'USE [{DATABASE}]')
                if f'USE {DATABASE}' in script_content_upper:
                    script_content = re.sub(f'USE {DATABASE}', '', script_content, flags=re.IGNORECASE)
                elif f'USE [{DATABASE}]' in script_content_upper:
                    #script_content = re.sub(f'USE [{DATABASE}]', '', script_content, flags=re.IGNORECASE)
                    script_content = script_content.replace(f'USE [{DATABASE}]', '')
                # replace getdate
                if 'GETDATE()' in script_content_upper and keyword13.upper() not in script_content_upper:
                    script_content = re.sub('getdate\(\)', keyword13, script_content, flags=re.IGNORECASE)
                elif 'SYSDATETIME()' in script_content_upper and keyword14.upper() not in script_content_upper:
                    script_content = re.sub('sysdatetime\(\)', keyword14, script_content, flags=re.IGNORECASE)
                # replace file group
                for file_group in file_groups:
                    if file_group in script_content:
                        script_content = script_content.replace(file_group, '[PRIMARY]')
                # remove /* */
                if '/*' in script_content and '*/' in script_content:
                    keyword_cnt = script_content.count('/*')
                    for i in range(1, keyword_cnt + 1):
                        start_position = script_content.find('/*')
                        end_position = script_content.find('*/')
                        if start_position >0 and (end_position > start_position):
                            script_content = script_content[:start_position] + script_content[end_position+2:]
                            i = i+1
                print('script_content:', script_content)
                commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

                # Execute each command separately
                print(script_file_path, 'start to deploy')
                for command in commands:
                   if command.strip():  # Skip empty commands
                        cursor.execute(command)
                        conn.commit()

                print('Deploy successful')
            else:
                print(script_file_path, "doesn't deploy")
    else:
        print(script_file_path, "doesn't exist")

cursor.close()
conn.close()


############################################## Deploy Auto
repo_path = rf"C:\work\code\{branch}"
repo = git.Repo(repo_path)
if repo.active_branch != 'main':
    repo.git.checkout('main')
print("Pull from GitHub: " + branch)
repo.remotes.origin.pull()

# Retrieve the deployment sequence from the control table
today = datetime.date.today()
lookBackDay = 1
#endDate = today.strftime('%Y-%m-%d')
endDate = '2025-03-03'
#startDate = (today + datetime.timedelta(days=-lookBackDay)).strftime("%Y-%m-%d")
startDate = '2025-02-16'
SQL1 = ("SELECT * FROM ( "
        "SELECT DISTINCT RIGHT(DBSourceCode,CHARINDEX('/', REVERSE(DBSourceCode))-1) AS FileName, CodeSubmitUTCDate, "
        "CASE WHEN DBSourceCode like '%/Tables/%' THEN 'table' "
	         "WHEN DBSourceCode like '%/Stored Procedures/%' THEN 'sp' "
		     "WHEN DBSourceCode like '%/Types/%' THEN 'type' "
		"END AS type "
        ",ROW_NUMBER() OVER(PARTITION BY DBSourceCode ORDER BY CodeSubmitUTCDate) AS num "
        "FROM dbo.DBESubmittedDBCode "
      f"WHERE DBSourceCodeBranch = '{branch}' AND ReviewStatus='APPROVED' "
      f"AND CodeSubmitUTCDate >= '{startDate}' AND CodeSubmitUTCDate < '{endDate}' "
       "AND (DBSourceCode like '%/Tables/%' OR DBSourceCode like '%/Stored Procedures/%' OR DBSourceCode like '%/Types/%') "
       #"AND DBESubmittedDBCodeKey in (22720, 22725, 22458) "
        ") as a WHERE num=1 "
       "ORDER BY CodeSubmitUTCDate, type DESC ")  #run type before sp
print('SQL:', SQL1)
cursor_etl.execute(SQL1)
rows = cursor_etl.fetchall()
print('Get FileName from DBESubmittedDBCode')

conn_auto = pyodbc.connect(conn_config)
cursor_auto = conn_auto.cursor()
print('Connect to Azure DEV server')

# get object list
SQL2 = "SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
cursor_auto.execute(SQL2)
existing_table_list = cursor_auto.fetchall()
table_list = []
for row in existing_table_list:
    table = row[0]
    table_list.append(table)
#print('table_list:', table_list)
SQL3 = ("SELECT routine_schema + '.' + routine_name "
       "FROM INFORMATION_SCHEMA.ROUTINES "
       "WHERE ROUTINE_TYPE = 'PROCEDURE' ")
cursor_auto.execute(SQL3)
existing_sp_list = cursor_auto.fetchall()
sp_list = []
for row in existing_sp_list:
    sp = row[0]
    sp_list.append(sp)
SQL4 = ("SELECT s.name + '.' + t.name "
        "FROM sys.types AS t "
        "LEFT JOIN sys.schemas AS s "
        "ON t.schema_id = s.schema_id")
cursor_auto.execute(SQL4)
existing_type_list = cursor_auto.fetchall()
type_list = []
for row in existing_type_list:
    user_type = row[0]
    type_list.append(user_type)
cursor_auto.close()
conn_auto.close()
print('Get object list')

# deploy differ code to Azure dev
conn = pyodbc.connect(conn_config)
cursor = conn.cursor()
print('Connect to Azure DEV server')
folder_path = rf"C:\work\code\{branch}"

for row in rows:
    file_name = row[0]
    object_name = file_name.replace('.sql','')
    script_type = row.type
    if script_type == 'table' and object_name not in table_list:
        print('folder_path:', folder_path)
        script_file_path = folder_path + r'\Tables\\' + file_name
        print('script_file_path:', script_file_path)
        if os.path.exists(script_file_path):
            print(script_file_path, 'exists')
            content_conversion = timezone_conversion(DATABASE, file_name, 'table')
            script_content = content_conversion[3]
            commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

            # Execute each command separately
            print(script_file_path, 'start to deploy')
            for command in commands:
                if command.strip():  # Skip empty commands
                    cursor.execute(command)
                    conn.commit()
            table_list.append(object_name)
            print('Deploy successful')
        else:
            print(script_file_path, "doesn't deploy")
    elif script_type == 'sp' and object_name not in sp_list:
        print('SP', object_name, "doesn't exist")
        script_file_path = folder_path + r'\Stored Procedures\\' + file_name
        print('script_file_path:', script_file_path)
        if os.path.exists(script_file_path):
            print(script_file_path, 'exists')
            content_conversion = timezone_conversion(DATABASE, file_name, 'sp')
            script_content = content_conversion[3]
            commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

            # Execute each command separately
            print('script_content:', script_content)
            print(script_file_path, 'start to deploy')
            for command in commands:
                if command.strip():  # Skip empty commands
                    cursor.execute(command)
                    conn.commit()
            sp_list.append(object_name)
            print('Deploy successful')
        else:
            print(script_file_path, "doesn't exist")
    elif script_type == 'sp' and object_name in sp_list:
        print('SP', object_name, "exist")
        script_file_path = folder_path + r'\Stored Procedures\\' + file_name
        print('script_file_path:', script_file_path)
        if os.path.exists(script_file_path):
            print(script_file_path, 'exists')
            content_conversion = timezone_conversion(DATABASE, file_name, 'sp')
            script_content = content_conversion[3]

            script_content = re.sub('CREATE PROC', 'ALTER PROC', script_content, flags=re.IGNORECASE)
            script_content = re.sub('CREATE\s*PROCEDURE', 'ALTER PROCEDURE', script_content, flags=re.IGNORECASE)
            commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

            # Execute each command separately
            print('script_content:', script_content)
            print(script_file_path, 'start to deploy')
            for command in commands:
                if command.strip():  # Skip empty commands
                    cursor.execute(command)
                    conn.commit()
            sp_list.append(object_name)
            print('Deploy successful')
        else:
            print(script_file_path, "doesn't exist")
    elif script_type == 'type' and object_name not in type_list:
        print('type', object_name, "exist")
        script_file_path = folder_path + r'\Types\User-defined Data Types\\' + file_name
        print('script_file_path:', script_file_path)
        if os.path.exists(script_file_path):
            print(script_file_path, 'exists')
            content_conversion = timezone_conversion(DATABASE, file_name, 'type')
            script_content = content_conversion[3]
            commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

            # Execute each command separately
            print(script_file_path, 'start to deploy')
            for command in commands:
                if command.strip():  # Skip empty commands
                    cursor.execute(command)
                    conn.commit()
            type_list.append(object_name)
            print('Deploy successful')
        else:
            print(script_file_path, "doesn't exist")
    else:
        print(file_name, "exists")

cursor.close()
conn.close()
